package org.springcloud.wisdom.spring_01_canteen.notice;

import org.apache.ibatis.annotations.Insert;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;

import java.util.List;

@Mapper
public interface NoticeDao {
    //发布公告
    @Insert("INSERT INTO notice (id,title,content,publishdt,identity,ispublic,reason,isdel,type) " +
            "VALUES (#{id}, #{title}, #{content}, #{publishdt}, #{identity}, #{ispublic}, #{reason}, #{isdel}, #{type})")
    int InsertNotice(Notice notice);

    //查看全部公告
    @Select("SELECT title,content,identity,publishdt,ispublic,reason FROM notice WHERE ispublic = '同意' " +
            "AND isdel=0 ORDER BY publishdt DESC " +
            "LIMIT #{start}, #{size}")
    List<Notice> getNotice(
            @Param("start") int start,
            @Param("size") int size);

    @Select("SELECT count(*) FROM notice WHERE ispublic = '同意' " +
            "AND isdel=0 ORDER BY publishdt DESC")
    public int CountNotice();


    //////////////用户///////////////
    //查看迎新模块公告
    @Select("SELECT title,content,publishdt,identity FROM notice " +
            "WHERE ispublic = '同意' AND isdel = 0 AND type = 1 " +
            "ORDER BY publishdt DESC " +
            "LIMIT #{start}, #{size}")
    List<Notice> getNotice1(
            @Param("start") int start,
            @Param("size") int size);

    @Select("SELECT count(*) FROM notice " +
            "WHERE ispublic = '同意' AND isdel = 0 AND type = 1 " +
            "ORDER BY publishdt DESC ")
    public int CountNotice1();

    //查看食堂模块公告
    @Select("SELECT title,content,publishdt,identity FROM notice WHERE ispublic = '同意' " +
            "AND isdel=0 AND type = 2 " +
            "ORDER BY publishdt DESC " +
            "LIMIT #{start}, #{size}")
    List<Notice> getNotice2(
            @Param("start") int start,
            @Param("size") int size);

    @Select("SELECT count(*) FROM notice WHERE ispublic = '同意' " +
            "AND isdel=0 AND type = 2 " +
            "ORDER BY publishdt DESC ")
    public int CountNotice2();

    //查看社团模块公告
    @Select("SELECT title,content,publishdt,identity FROM notice WHERE ispublic = '同意'" +
            "AND isdel=0 AND type = 3 " +
            "ORDER BY publishdt DESC " +
            "LIMIT #{start}, #{size}")
    List<Notice> getNotice3(
            @Param("start") int start,
            @Param("size") int size);

    @Select("SELECT count(*) FROM notice WHERE ispublic = '同意' " +
            "AND isdel=0 AND type = 3 " +
            "ORDER BY publishdt DESC ")
    public int CountNotice3();

    //////////////后台///////////////
    //查看迎新模块公告
    @Select("SELECT title,content,identity,publishdt,ispublic,reason FROM notice " +
            "WHERE  isdel = 0 AND type = 1 " +
            "ORDER BY publishdt DESC " +
            "LIMIT #{start}, #{size}")
    List<Notice> getNotice11(
            @Param("start") int start,
            @Param("size") int size);

    @Select("SELECT count(*) FROM notice " +
            "WHERE  isdel = 0 AND type = 1 " +
            "ORDER BY publishdt DESC ")
    public int CountNotice11();

    //查看食堂模块公告
    @Select("SELECT title,content,identity,publishdt,ispublic,reason FROM notice WHERE  " +
            "isdel=0 AND type = 2 " +
            "ORDER BY publishdt DESC " +
            "LIMIT #{start}, #{size}")
    List<Notice> getNotice22(
            @Param("start") int start,
            @Param("size") int size);

    @Select("SELECT count(*) FROM notice WHERE  " +
            "isdel=0 AND type = 2 " +
            "ORDER BY publishdt DESC ")
    public int CountNotice22();

    //查看社团模块公告
    @Select("SELECT title,content,identity,publishdt,ispublic,reason FROM notice WHERE " +
            "isdel=0 AND type = 3 " +
            "ORDER BY publishdt DESC " +
            "LIMIT #{start}, #{size}")
    List<Notice> getNotice33(
            @Param("start") int start,
            @Param("size") int size);

    @Select("SELECT count(*) FROM notice WHERE " +
            "isdel=0 AND type = 3 " +
            "ORDER BY publishdt DESC ")
    public int CountNotice33();
}
